package com.goldentrail.db;

import java.sql.*;
import java.util.Enumeration;
import java.util.Hashtable;


/**
 * 
 * 
 * @author Dey, Saumen
 * @version 1.0, Oct 09, 2011
 *
 */
public class DBOperation {
	
	Statement     stmt; 
	DBConnection  dbCon;
	Connection    con;

	public DBOperation(boolean isLocal) {
		try {
			dbCon = new DBConnection();
			con   = dbCon.getConnection(isLocal);
			stmt  = con.createStatement();
		} catch( Exception e ) {
			e.printStackTrace();
		}
	}

	private void prepareLOV(String provType, String lovType) {
		Hashtable<Integer, String> ht = new Hashtable<Integer, String>();
		ht.put(new Integer(1), provType);
		ht.put(new Integer(2), lovType);
		execProc(ht, "get_lov(?,?)");
	}

	public QueryResult getLOV(String provType, String lovType) {
		
		prepareLOV(provType, lovType);
		
		StringBuffer qry = new StringBuffer();
		qry.append("   SELECT node");
		qry.append("     FROM lov");
		System.out.println("qry.toString(): " + qry.toString());
		QueryResult qr = queryExec(qry.toString());
		return qr; 		
	}

	public Hashtable<String, QueryResult> getAllLOVs(String provType) {
		
		QueryResult qr1 = getLOV(provType, "id");
		QueryResult qr2 = getLOV(provType, "od");
		QueryResult qr3 = getLOV(provType, "i");
		
		Hashtable<String, QueryResult> ht = new Hashtable<String, QueryResult>();
		ht.put("id", qr1);
		ht.put("od", qr2);
		ht.put("i",  qr3);
		
		return ht; 		
	}
	
	private void prepareDEP(String provType, 
			                 String depType,
			                 String ft_start,
			                 String ft_end,
			                 String ft_through) {
		Hashtable<Integer, String> ht = new Hashtable<Integer, String>();
		ht.put(new Integer(1), provType);
		ht.put(new Integer(2), depType);
		ht.put(new Integer(3), ft_start);
		ht.put(new Integer(4), ft_end);
		ht.put(new Integer(5), ft_through);
		execProc(ht, "get_dep(?,?,?,?,?)");
	}

	public QueryResult getDEP(String provType, 
            				  String depType,
            				  String ft_start,
            				  String ft_end,
            				  String ft_through) {
		
		prepareDEP(provType, 
                   depType,
                   ft_start,
                   ft_end,
                   ft_through);
                
		StringBuffer qry = new StringBuffer();
		qry.append("   SELECT snode, enode, type");
		qry.append("     FROM dep");
		qry.append(" ORDER BY snode");
		System.out.println("qry.toString(): " + qry.toString());
		QueryResult qr = queryExec(qry.toString());
		return qr; 		
	}

	
	public boolean isInDb(String tableName, 
			  			  String columnName,
			  			  String columnValue) {

		boolean isThere = false;
		
		StringBuffer qry = new StringBuffer();
		qry.append("   SELECT " + columnName);
		qry.append("     FROM " + tableName);
		qry.append("    WHERE " + columnName + " = '" + columnValue + "'");
		System.out.println("qry.toString(): " + qry.toString());
		QueryResult qr = queryExec(qry.toString());
		
		if (qr.getResult().length>0)
			isThere = true;
		
		return isThere; 		
	}
	
	public void execProc (Hashtable<Integer, String> ht, String pName) {
		Integer key  = null;
		try {
			CallableStatement cs = con.prepareCall("call "+pName);
			for (Enumeration<Integer> e = ht.keys() ; e.hasMoreElements() ;) {
				key = e.nextElement();
				if (ht.get(key).equals("NULL")) {
					cs.setNull(key.intValue(),Types.CHAR);
				} else {
					cs.setString(key.intValue(), ht.get(key));	
				}
		     }
			System.out.println(cs.toString());
			cs.execute();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}	
	
	public QueryResult queryExec(String qry) {
		int rowCnt = 0;
		int colCnt = 0;
		String[][] result = null;
		String[]   header = null;
		try {
			stmt.execute(qry);
			ResultSet rs = stmt.getResultSet();
			ResultSetMetaData rsm = rs.getMetaData();
			rs.last();
			rowCnt = rs.getRow();
			colCnt = rsm.getColumnCount();
			header = new String[colCnt];
			for (int i=1;i<=colCnt; i++) {
				header[i-1] = rsm.getColumnName(i);
			}
			result = new String[rowCnt][colCnt];
			rs.beforeFirst();
			while(rs.next()) {
				for (int j=1;j<=colCnt; j++) {
					result[rs.getRow()-1][j-1] = rs.getString(j);
				}
			}
			rs.close();
		} catch( Exception e ) {
			e.printStackTrace();
		}	
		QueryResult qr = new QueryResult();
		qr.setHeader(header);
		qr.setResult(result);
		return qr;
	}
	
	public void insert(Hashtable<String, String> ht, String tName) {
		StringBuffer insStmt = new StringBuffer();
		StringBuffer valStmt = new StringBuffer();
		int    size = ht.size();
		int    cnt  = 0;
		String key = null;
		try {
			insStmt.append("insert into " + tName + " (");
			valStmt.append("values (");
			for (Enumeration<String> e = ht.keys() ; e.hasMoreElements() ;) {
				cnt++;
				key = e.nextElement();
				if (cnt == size) {
					insStmt.append(key); insStmt.append(") ");
					valStmt.append(ht.get(key)); valStmt.append("); ");
				} else {
					insStmt.append(key); insStmt.append(", ");
					valStmt.append(ht.get(key)); valStmt.append(", ");
				}
		     }
			String sql = insStmt.toString() + valStmt.toString();
			System.out.println(sql);
			stmt.execute(sql);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
}